SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[SP_TMSACA_MENS_Q01]
/*-------------------------------------------------------*/
/*---Empresa              : OFISIS S.A.                --*/
/*---Cliente              : CASSINELLI              --*/
/*---Sistema              : Tesoreria              --*/
/*---M«dulo               : Empresa                    --*/
/*---Programa             : Consulta de saldos         --*/
/*---                       Movimiento Caja 	       --*/
/*---Script               : TMSACA_M.SQL	       --*/
/*---Nombre SP            : SP_TMSACA_MENS_Q01        --*/
/*---Desarrollado por     : Antonio Rojas	       --*/
/*---Fecha Creaci«n       : 19/07/1999                 --*/
/*---Base Datos           : Microsoft Sql Server       --*/
/*---Versi«n              : 7.0                        --*/
/*---Invoca a SP          :                            --*/
/*-------------------------------------------------------*/

/*-------------------------------------------------------*/
/*---Modificado 1 por     :                            --*/
/*---Fecha Modificaci«n   :                            --*/ 
/*---Detalle Modificaci«n :                            --*/
/*-------------------------------------------------------*/

/*-------------------------------------------------------*/
/*---Comentarios                                       --*/
/*---                                                  --*/
/*---                                                  --*/
/*-------------------------------------------------------*/

/*------------------------------------------------------ */
/* Drop Proc SP_TMSACA_MENS_Q01			 	 */
/* Grant All On SP_TMSACA_MENS_Q01_DENIS To Public	 	 */
/* SP_TMSACA_MENS_Q01 '10', null ,'1999','07'   	 */
/*-------------------------------------------------------*/
@ISCO_USUA   	 TD_VC_008_USUA,
@ISAA_CAJA   	 TD_VC_004,
@ISMM_CAJA   	 TD_VC_002,
@ISCA_WHER_TEMP  Varchar(300)
	As
Create Table #TWSACA_MENS_Q01_1
(
 CO_EMPR VARCHAR(2),
 DE_NOMB VARCHAR(50),	
 CO_CAJA VARCHAR(8),
 DE_CAJA VARCHAR(50),	
 IM_SALD_INIC NUMERIC(16,4),
 IM_INGR NUMERIC(16,4),
 IM_EGRE NUMERIC(16,4),
 IM_TOTA NUMERIC(16,4),
 CO_MONE VARCHAR(3)
)

/*************************************
 Declaraci½n de variables de trabajo
**************************************/
Declare
/**************************************
 Declaraci½n de las variables del cursor
***************************************/
@CSCO_EMPR	TD_VC_002 ,
@CSDE_NOMB    	TD_VC_050 ,
@CSCO_CAJA	TD_VC_008 ,
@CSDE_CAJA  	TD_VC_050 ,
@CNIM_SALD_INIC	TD_NU_016_004 ,
@CNIM_INGR     	TD_NU_016_004 ,
@CNIM_EGRE     	TD_NU_016_004,
@CSCO_MONE	TD_VC_003,
/*Variables*/
@VNIM_INGR     	TD_NU_016_004 ,
@VSCO_EMPR	TD_VC_002 ,
@VNIM_EGRE     	TD_NU_016_004 ,
@VNIM_SALD_INIC TD_NU_016_004 ,
@VNIM_SALD_ANTE TD_NU_016_004 ,
@VNIM_TOTA	TD_NU_016_004 ,
@VNNU_CAJA      TD_IN_001 ,
@VSAA_CAJA	TD_VC_004 ,
@VSMM_CAJA	TD_VC_002

/**************************************
Declaraci½n de cursor
***************************************/
Exec("
Declare CU_TMSACA_MENS_I01 CURSOR
For
 Select  TMSACA_MENS.CO_EMPR, TMEMPR.DE_NOMB, TMSACA_MENS.CO_CAJA, TMCAJA.DE_CAJA, Isnull(TMSACA_MENS.IM_SALD_INIC,0), 
	 Isnull(TMSACA_MENS.IM_INGR,0), Isnull(TMSACA_MENS.IM_EGRE,0), TMCAJA.CO_MONE
 From 	 TMSACA_MENS , TMCAJA , TMEMPR 
 Where   TMSACA_MENS.AA_CAJA = '" + @ISAA_CAJA + "' 
  	 And TMSACA_MENS.MM_CAJA = '" +  @ISMM_CAJA  + "'
  	 And TMSACA_MENS.CO_EMPR = TMCAJA.CO_EMPR
  	 And TMSACA_MENS.CO_CAJA = TMCAJA.CO_CAJA
  	 And TMSACA_MENS.CO_EMPR = TMEMPR.CO_EMPR
  	 And Exists ( Select  * 
		      From    TTUSER_CAJA 
		      Where   TMSACA_MENS.CO_EMPR = TTUSER_CAJA.CO_EMPR
			      And TMSACA_MENS.CO_CAJA  = TTUSER_CAJA.CO_CAJA
			      And TTUSER_CAJA.CO_USUA = '" + @ISCO_USUA + "'
			      And TTUSER_CAJA.ST_REPO = 'S' )"
	 + @ISCA_WHER_TEMP)

Open CU_TMSACA_MENS_I01
Fetch CU_TMSACA_MENS_I01 INTO @CSCO_EMPR , @CSDE_NOMB , @CSCO_CAJA , @CSDE_CAJA , @CNIM_SALD_INIC , @CNIM_INGR , @CNIM_EGRE  
, @CSCO_MONE

WHILE (@@FETCH_STATUS = 0)
Begin
 If (@@FETCH_STATUS < 0)
   Begin
    RAISERROR 20001 'FIN DE ARCHIVO'
    Close CU_TMSACA_MENS_I01
    Deallocate CU_TMSACA_MENS_I01
    Return
   End
 /*************************************
 Hallar el saldo del mes anterior
 *************************************/
 Select @VNIM_SALD_ANTE = 0
 Select @VNIM_SALD_INIC = 0
 Select @VNIM_TOTA = 0	   

 If @ISMM_CAJA = '01'
  Begin 
   Select @VSMM_CAJA = '12'
   Select @VSAA_CAJA = @ISAA_CAJA - 1
  End
 else
  Begin
   Select @VSMM_CAJA = @ISMM_CAJA - 1
   select @VSAA_CAJA = @ISAA_CAJA
  End


 If Len(@VSMM_CAJA)=1
  Select @VSMM_CAJA = '0'+ @VSMM_CAJA

 /*************************************
 Nœmero de Registros de periodo anterior
 **************************************/
 Select @VNNU_CAJA = count(*)
 From 	TMSACA_MENS
 where 	CO_EMPR = @CSCO_EMPR
 	And CO_CAJA = @CSCO_CAJA
 	And AA_CAJA = @VSAA_CAJA
 	And MM_CAJA = @VSMM_CAJA   
 If @VNNU_CAJA > 0 
  Begin
   Select  @VNIM_SALD_ANTE = ISNULL( IM_SALD_INIC, 0) , @VNIM_INGR= ISNULL(IM_INGR,0) , @VNIM_EGRE=ISNULL(IM_EGRE,0)
   From    TMSACA_MENS	
   Where   CO_EMPR  = @CSCO_EMPR
   	   And CO_CAJA = @CSCO_CAJA
   	   And AA_CAJA = @VSAA_CAJA
   	   And MM_CAJA = @VSMM_CAJA

   Select  @VNIM_SALD_INIC = @VNIM_SALD_ANTE + @VNIM_INGR - @VNIM_EGRE
   Select  @VNIM_TOTA = @VNIM_SALD_INIC + @CNIM_INGR - @CNIM_EGRE 

  End  
 Else
  Begin		
   Select @VNIM_SALD_INIC = @CNIM_SALD_INIC 
   Select @VNIM_TOTA = @VNIM_SALD_INIC + @CNIM_INGR - @CNIM_EGRE      
  End
 /*************************************/
 /*Asigna valores a la tabla temporal*/
 /*************************************/
 Insert Into #TWSACA_MENS_Q01_1(CO_EMPR,DE_NOMB,CO_CAJA,DE_CAJA,IM_SALD_INIC,IM_INGR,IM_EGRE,IM_TOTA,CO_MONE)
 Values  (@CSCO_EMPR , @CSDE_NOMB , @CSCO_CAJA , @CSDE_CAJA , @VNIM_SALD_INIC , @CNIM_INGR , @CNIM_EGRE , @VNIM_TOTA , 
@CSCO_MONE)

 Fetch  CU_TMSACA_MENS_I01 
 Into 	@CSCO_EMPR, @CSDE_NOMB, @CSCO_CAJA, @CSDE_CAJA, 
	@CNIM_SALD_INIC, @CNIM_INGR, @CNIM_EGRE, @CSCO_MONE

End
Close CU_TMSACA_MENS_I01
Deallocate CU_TMSACA_MENS_I01

Select 	  * 
From 	  #TWSACA_MENS_Q01_1
Order By  CO_EMPR , CO_MONE 
Return
/*--------------------------------------F­n-----------------------------------------------*/


GO
